package com.rlyy.rsj.dao;

import com.rlyy.aibase.db.QueryRunner;
import com.rlyy.aibase.utils.base.TableNode;
import com.rlyy.rsj.table.SYS_ORGPTYPE;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;


public class OrgpDao extends BaseDao {

    private static final OrgpDao instance = new OrgpDao();
    private static final String tableName = "sys.sys_orgp";

    public static OrgpDao getInstance() {
        return instance;
    }

    /**
     * 定时任务，每两分钟更新一次合同表的状态
     */

    public void updateOrgpStatus(Connection conn) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer updatesql = new StringBuffer();
        updatesql.append("update sys.sys_orgp set orgp_status =" +
                " case when " +
                " (orgp_type = '2' and current_date >= orgp_contractime and current_date < orgp_ctime) or (orgp_type = '2' and current_date >= orgp_contractime and current_date < orgp_ctime) " +
                " then  '30' " +
                " when " +
                " orgp_type = '2' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_billmethod = '2' and orgp_buyamount > orgp_useamount) " +
                " then '10' " +
                " when " +
                " orgp_type = '2' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_billmethod = '3' and orgp_buyamount >= orgp_useamount) " +
                " then '10' " +
                " when " +
                " orgp_type = '2' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and orgp_billmethod = '1' " +
                " then '10' " +
                " when " +
                "  orgp_type = '2'  and (current_date > orgp_endtime) " +
                " then '50'" +
                " when " +
                " orgp_type = '2' and orgp_billmethod = '2' and orgp_buyamount <= orgp_useamount " +
                " then '50'" +
                " when " +
                " orgp_type = '1' and (current_date > orgp_endtime)  " +
                " then '60'" +
                " when " +
                " orgp_type = '1' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_billmethod = '3') " +
                " then '20'" +
                " when " +
                " orgp_type = '1' and (orgp_buyamount <= orgp_useamount) " +
                " then '60'" +
                " when " +
                " orgp_type = '1' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_useamount > orgp_buyamount*0.5) " +
                " then '55'" +
                " when " +
                " orgp_type = '1' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_buyamount > orgp_useamount) " +
                " then '20'" +
                " else orgp_status end;");

        queryRunner.update(conn, updatesql.toString());

    }


    public void updateProdStatus(Connection conn) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer updatesql = new StringBuffer();

        updatesql.append(" 	truncate table sys.sys_status;																									")
                .append(" 	with cte_2 as (                                                                                                                 ")
                .append(" 	 with cte_1 as (                                                                                                                ")
                .append(" 	  select                                                                                                                        ")
                .append(" 	    orgp_id,                                                                                                                    ")
                .append(" 	    orgp_org_id,                                                                                                                ")
//                .append(" 	    regexp_split_to_table(orgp_prod_id, ',') as orgp_prod_id,                                                                   ")
//                .append(" 	    regexp_split_to_table(orgp_prod_charge, ',') as orgp_prod_charge,                                                           ")
                // 去除状态为40或70的干扰，否则orgp_prod_charge为null，导致插入失败
                .append(" 	    CASE WHEN orgp_prod_charge is null or orgp_prod_charge = '' THEN regexp_split_to_table(orgp_prod_uid, ',') ELSE regexp_split_to_table(orgp_prod_charge, ',' ) END AS orgp_prod_charge,                                                           ")
                .append(" 	    orgp_status,                                                                                                                ")
                .append(" 	    orgp_contractime                                                                                                            ")
                .append(" 	  from sys.sys_orgp                                                                                                             ")
                .append(" 	  left join sys.sys_user u on orgp_org_id = u.user_org_id                                                                    ")
                .append(" 	  where orgp_orgpstatus = '1' and  user_status = '1'                                                                            ")
                .append(" 	 )                                                                                                                              ")
                .append(" 	 select                                                                                                                         ")
                .append(" 	  orgp_id,                                                                                                                      ")
                .append(" 	  orgp_org_id,                                                                                                                  ")
//                .append(" 	  orgp_prod_id,                                                                                                                 ")
                .append(" 	  p.prod_id as orgp_prod_id,                                                                                                    ")
//                .append(" 	  split_part(orgp_prod_charge, '#', 1) as orgp_prod_uid,                                                                        ")
                .append(" 	  split_part(orgp_prod_charge, '#', 2) as orgp_prod_charge_mode,                                                                ")
                .append(" 	  orgp_status,                                                                                                                  ")
                .append(" 	  orgp_contractime,                                                                                                             ")
//                .append(" 	  row_number() over (partition by (orgp_org_id, orgp_prod_id) order by (orgp_status, orgp_contractime)) as num                  ")
                .append(" 	  row_number() over (partition by (orgp_org_id, p.prod_id) order by (orgp_status, orgp_contractime)) as num                     ")
                .append(" 	 from cte_1                                                                                                                     ")
                .append(" 	 LEFT JOIN sys.sys_prod p ON split_part(cte_1.orgp_prod_charge, '#', 1) = p.prod_uid                                            ")
                .append(" 	)                                                                                                                               ")
                .append(" 	insert into sys.sys_status                                                                                                      ")
                .append(" 	(sta_org_id,sta_prod_id,sta_status,sta_orgp_id,sta_charge_mode)                                                                 ")
                .append(" 	select                                                                                                                          ")
                .append(" 	 orgp_org_id,                                                                                                                   ")
                .append(" 	 orgp_prod_id,                                                                                                                  ")
                .append(" 	 orgp_status,                                                                                                                   ")
                .append(" 	 orgp_id,                                                                                                                       ")
                .append(" 	 orgp_prod_charge_mode                                                                                                          ")
                .append(" 	from cte_2 where num = 1;                                                                                                       ");

        queryRunner.update(conn, updatesql.toString());

    }

    public TableNode findSysStatus(Connection conn) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer findsql = new StringBuffer();

        findsql.append("SELECT s.sta_status,s.sta_orgp_id,s.sta_prod_id,s.sta_charge_mode,a.aut_keyid,p.prod_id,p.prod_uid from sys.sys_status s " +
                "left join sys.sys_authon a on s.sta_org_id = a.aut_org_id " +
                "left join (SELECT pro.prod_uid,pro.prod_id FROM  sys.sys_prod pro) p on p.prod_id = s.sta_prod_id ");
        return queryRunner.queryLinkTableNode(conn, findsql.toString());
    }

    public TableNode findOrgpNotInSysStatus(Connection conn){
        StringBuffer sql = new StringBuffer();
        sql.append(" with cte_1 as ( ")
            .append(" select orgp_org_id, regexp_split_to_table(orgp_prod_id, ',') as orgp_prod_id")
            .append(" from sys.sys_orgp where orgp_orgpstatus = '0'")
            .append(" ) ")
            .append(" SELECT a.aut_keyid,p.prod_uid FROM cte_1 ")
            .append(" left join sys.sys_authon a on cte_1.orgp_org_id = a.aut_org_id ")
            .append(" left join sys.sys_prod p on p.prod_id = cte_1.orgp_prod_id ")
            .append(" WHERE (cte_1.orgp_org_id,cte_1.orgp_prod_id) not in (SELECT sta_org_id,sta_prod_id FROM sys.sys_status) ");
        return getQueryRunner().queryLinkTableNode(conn, sql.toString());
    }

    public TableNode getBillInfoByOrgId(String orgId, Connection conn) {
        String fields = "orgp_id,orgp_prod_id,orgp_prod_name,orgp_buyamount,orgp_useamount,orgp_utime,orgp_billmethod";
        String conditions = " orgp_org_id = ? and (orgp_status = '%s' or " + "orgp_status = '%s' or "+
                "( orgp_status = '%s' and to_char( orgp_utime, 'YYYY-MM' ) = ? ))" +
                "and orgp_orgpstatus = '1'";
        String sql = String.format(select, fields, tableName, String.format(conditions, SYS_ORGPTYPE.Orgp_Status_Service,SYS_ORGPTYPE.Orgp_Status_Ineffective, SYS_ORGPTYPE.Orgp_Status_ServiceOver));
        return getQueryRunner().queryLinkTableNode(conn, sql, orgId, new SimpleDateFormat("yyyy-MM").format(new java.util.Date()));
    }
}
